const mysql = require('mysql')

const {host, user, password, database} = require('./config')
const {debug} = require('../utils/constant')
const {isObject} = require('../utils')
const e = require('express')


function connect(params) {
  return mysql.createConnection({
    host,
    user,
    password,
    database,
    multipleStatements: true  // 允许每条mysql语句有多条查询
  })
}

function querySql(sql) {
  const conn = connect()
  debug && console.log(sql)
  return new Promise((resolve, reject) => {
    try {
      conn.query(sql, (err, results) => {
        if(err) {
          debug && console.log('查询失败，原因：' + JSON.stringify(err))
          reject(err)
        } else {
          debug && console.log('查询成功：' + JSON.stringify(results))
          resolve(results)
        }
      })
    } catch (e) {
      reject(e)
    }finally {
      conn.end()  //释放连接，不然连接一直在内存当中
    }
  })
}

function queryOne(sql) {
  return new Promise((resolve, reject) => {
    querySql(sql).then(results => {
      if (results && results.length > 0) {
        resolve(results[0])
      } else {
        resolve(null)
      }
    }).catch(err => {
      reject(err)
    })
  })
}

function insert(model, tableName) {
  console.log('model', model)
  return new Promise((resolve, reject) => {
    if (!isObject(model)) {
      reject(new Error('插入数据库失败'))
    }else {
      const keys = []
      const values = []
      Object.keys(model).forEach(key => {
        if(model.hasOwnProperty(key)) {
          // 避免某些key在数据库中是关键字造成插入失败
          keys.push(`\`${key}\``)
          values.push(`'${model[key]}'`)
        }
      })
      if(keys.length > 0 && values.length > 0) {
        let sql = `INSERT INTO \`${tableName}\` (`
        const keysString = keys.join(',')
        const valuesString = values.join(',')
        sql = `${sql}${keysString}) VALUES (${valuesString})`
        debug && console.log(sql)

        const conn = connect()
        try {
          conn.query(sql, (err, result) => {
            if(err) {
              reject(err)
            } else {
              resolve(result)
            }
          })
        } catch (error) {
          reject(error)
        } finally {
          conn.end()
        }
      } else {
        reject(new Error('插入数据库失败，对象中没有任何属性'))
      }
    }
  })
}

function update(model, tableName, where){
  return new Promise((resolve, reject) => {
    if (!isObject(model)) {
      reject(new Error('插入数据库失败, 插入数据非对象'))
    } else {
      // update table set key=value
      const entry = []
      Object.keys(model).forEach(key => {
        if (model.hasOwnProperty(key)) {
          entry.push(`\`${key}\`='${model[key]}'`)
        }
      })
      if (entry.length > 0) {
        let sql = `UPDATE \`${tableName}\` SET`
        sql = `${sql} ${entry.join(',')} ${where}`
        
        debug && console.log(sql)
        const conn = connect()
        try {
          conn.query(sql, (err, result) => {
            if(err) {
              reject(err)
            } else {
              resolve(result)
            }
          })
        } catch (error) {
          reject(error)
        } finally {
          conn.end()
        }
      }
    }
  })
}

function and(where, k, v) {
  if (where == 'where') {
    return `${where} \`${k}\`='${v}'`
  } else {
    return `${where} and \`${k}\`='${v}'`
  }
}

function andLike(where, k, v) {
  if (where == 'where') {
    return `${where} \`${k}\` like '%${v}%'`
  } else {
    return `${where} and \`${k}\` like '%${v}%'`
  }
}

module.exports = {
  querySql,
  queryOne,
  insert,
  update,
  and,
  andLike
}